from fastapi import APIRouter, Depends, Query
from sqlalchemy.orm import Session
from sqlalchemy import func
from datetime import datetime, timedelta
from typing import Optional, List, Dict

from database.connection import get_db
from database.models import User, Tools, WorkLike, WorkComment, LoginSession
from models.tools import ToolsResponse
from .admin import verify_admin_permission, User as AdminUser


router = APIRouter(prefix="/admin/analytics", tags=["数据统计"])


def _get_period_range(period: str, start: Optional[datetime] = None, end: Optional[datetime] = None):
    """
    计算统计区间的起止时间

    参数:
        period: 聚合周期，支持 today/week/month/quarter/year
        start: 可选自定义起始时间覆盖
        end: 可选自定义结束时间覆盖（开区间）

    返回:
        (start_dt, end_dt): 起止时间元组，end 为开区间
    """
    now = datetime.now()
    if start and end:
        return start, end
    if period == "today":
        s = now.replace(hour=0, minute=0, second=0, microsecond=0)
        e = s + timedelta(days=1)
    elif period == "week":
        s = (now - timedelta(days=now.weekday())).replace(hour=0, minute=0, second=0, microsecond=0)
        e = s + timedelta(days=7)
    elif period == "month":
        s = now.replace(day=1, hour=0, minute=0, second=0, microsecond=0)
        e = (s.replace(day=28) + timedelta(days=4)).replace(day=1)
    elif period == "quarter":
        q = (now.month - 1) // 3
        sm = q * 3 + 1
        s = now.replace(month=sm, day=1, hour=0, minute=0, second=0, microsecond=0)
        nm = sm + 3
        if nm > 12:
            e = s.replace(year=s.year + 1, month=1)
        else:
            e = s.replace(month=nm)
    elif period == "year":
        s = now.replace(month=1, day=1, hour=0, minute=0, second=0, microsecond=0)
        e = s.replace(year=s.year + 1)
    else:
        raise ValueError("invalid period")
    return s, e


@router.get("/overview", summary="概览统计")
async def analytics_overview(
    period: str = Query("today", regex="^(today|week|month|quarter|year)$"),
    start: Optional[datetime] = Query(None),
    end: Optional[datetime] = Query(None),
    db: Session = Depends(get_db),
    admin_user: AdminUser = Depends(verify_admin_permission),
):
    """
    概览统计接口

    返回指定周期的核心统计：访问量、用户量、活跃用户数、工具点击总数
    访问量近似为区间内登录会话数；工具点击总数短期使用累计值
    """
    s, e = _get_period_range(period, start, end)

    user_count = db.query(func.count()).select_from(User).filter(User.create_time >= s, User.create_time < e, User.is_delete == False).scalar() or 0

    visit_count = db.query(func.count()).select_from(LoginSession).filter(LoginSession.create_time >= s, LoginSession.create_time < e).scalar() or 0

    active_login_users = db.query(LoginSession.user_id).filter(LoginSession.create_time >= s, LoginSession.create_time < e, LoginSession.user_id != None).distinct().count()
    active_like_users = db.query(WorkLike.user_id).filter(WorkLike.create_time >= s, WorkLike.create_time < e).distinct().count()
    active_comment_users = db.query(WorkComment.user_id).filter(WorkComment.create_time >= s, WorkComment.create_time < e).distinct().count()
    active_users = max(active_login_users, active_like_users + active_comment_users)

    tool_clicks_total = db.query(func.sum(Tools.click_count)).scalar() or 0

    return {
        "code": 200,
        "msg": "success",
        "data": {
            "visit_count": visit_count,
            "user_count": user_count,
            "active_users": active_users,
            "tool_clicks_total": tool_clicks_total,
            "period": period,
            "start": s.isoformat(),
            "end": e.isoformat(),
        },
    }


@router.get("/hot-tools", summary="热门工具排行")
async def analytics_hot_tools(
    period: str = Query("today", regex="^(today|week|month|quarter|year)$"),
    limit: int = Query(10, ge=1, le=100),
    db: Session = Depends(get_db),
    admin_user: AdminUser = Depends(verify_admin_permission),
):
    """
    热门工具排行接口

    短期按累计点击数排序；后续可切换为按区间点击日志聚合
    """
    tools = db.query(
        Tools.id.label("tool_id"),
        Tools.title.label("name"),
        Tools.click_count.label("clicks")
    ).order_by(Tools.click_count.desc()).limit(limit).all()

    return {
        "code": 200,
        "msg": "success",
        "data": [{"tool_id": t.tool_id, "name": t.name, "clicks": t.clicks} for t in tools],
        "period": period,
    }


@router.get("/user-activity", summary="用户活跃度统计")
async def analytics_user_activity(
    period: str = Query("today", regex="^(today|week|month|quarter|year)$"),
    start: Optional[datetime] = Query(None),
    end: Optional[datetime] = Query(None),
    top_n: int = Query(10, ge=1, le=100),
    db: Session = Depends(get_db),
    admin_user: AdminUser = Depends(verify_admin_permission),
):
    """
    用户活跃度统计接口

    返回登录、点赞、评论的总量以及活跃 TOP 用户（按互动量排序）
    """
    s, e = _get_period_range(period, start, end)

    login_total = db.query(func.count()).select_from(LoginSession).filter(LoginSession.create_time >= s, LoginSession.create_time < e).scalar() or 0
    likes_total = db.query(func.count()).select_from(WorkLike).filter(WorkLike.create_time >= s, WorkLike.create_time < e).scalar() or 0
    comments_total = db.query(func.count()).select_from(WorkComment).filter(WorkComment.create_time >= s, WorkComment.create_time < e).scalar() or 0

    # TOP 用户：按点赞+评论综合排序
    user_like_counts = db.query(WorkLike.user_id.label("user_id"), func.count(WorkLike.id).label("likes")).filter(WorkLike.create_time >= s, WorkLike.create_time < e).group_by(WorkLike.user_id).subquery()
    user_comment_counts = db.query(WorkComment.user_id.label("user_id"), func.count(WorkComment.id).label("comments")).filter(WorkComment.create_time >= s, WorkComment.create_time < e).group_by(WorkComment.user_id).subquery()

    top_users = db.query(
        func.coalesce(user_like_counts.c.user_id, user_comment_counts.c.user_id).label("user_id"),
        (func.coalesce(user_like_counts.c.likes, 0) + func.coalesce(user_comment_counts.c.comments, 0)).label("actions"),
    ).outerjoin(
        user_comment_counts,
        user_like_counts.c.user_id == user_comment_counts.c.user_id
    ).order_by((func.coalesce(user_like_counts.c.likes, 0) + func.coalesce(user_comment_counts.c.comments, 0)).desc()).limit(top_n).all()

    return {
        "code": 200,
        "msg": "success",
        "data": {
            "activity_breakdown": {"login": login_total, "likes": likes_total, "comments": comments_total},
            "top_users": [{"user_id": u.user_id, "actions": u.actions} for u in top_users],
            "period": period,
            "start": s.isoformat(),
            "end": e.isoformat(),
        },
    }


@router.get("/timeseries", summary="时序数据")
async def analytics_timeseries(
    metric: str = Query("visits", regex="^(visits|users|activity)$"),
    period: str = Query("week", regex="^(today|week|month|quarter|year)$"),
    start: Optional[datetime] = Query(None),
    end: Optional[datetime] = Query(None),
    db: Session = Depends(get_db),
    admin_user: AdminUser = Depends(verify_admin_permission),
):
    """
    时序数据接口

    返回指定 metric 在给定周期内的按日聚合数据，用于图表展示
    metric 支持：visits（登录会话数）、users（新增用户数）、activity（点赞+评论数）
    """
    s, e = _get_period_range(period, start, end)

    data: List[Dict] = []
    day = s
    while day < e:
        next_day = day + timedelta(days=1)
        if metric == "visits":
            value = db.query(func.count()).select_from(LoginSession).filter(LoginSession.create_time >= day, LoginSession.create_time < next_day).scalar() or 0
        elif metric == "users":
            value = db.query(func.count()).select_from(User).filter(User.create_time >= day, User.create_time < next_day, User.is_delete == False).scalar() or 0
        else:
            likes = db.query(func.count()).select_from(WorkLike).filter(WorkLike.create_time >= day, WorkLike.create_time < next_day).scalar() or 0
            comments = db.query(func.count()).select_from(WorkComment).filter(WorkComment.create_time >= day, WorkComment.create_time < next_day).scalar() or 0
            value = (likes or 0) + (comments or 0)
        data.append({"date": day.strftime("%Y-%m-%d"), "value": value})
        day = next_day

    return {"code": 200, "msg": "success", "data": data, "metric": metric, "period": period}


@router.get("/dashboard", summary="统计总览（合并接口）")
async def analytics_dashboard(
    latest_limit: int = Query(10, ge=1, le=100, description="最近新增工具返回数量"),
    db: Session = Depends(get_db),
    admin_user: AdminUser = Depends(verify_admin_permission),
):
    """
    统计总览合并接口

    返回：
    - 概览：工具总数（启用）、注册用户、今日访问、工具点击累计
    - 趋势：近7/30/90天每日访问量
     - 列表：最近新增启用工具（limit 可配置）、热门工具排行前10
    """
    now = datetime.now()
    today_start = now.replace(hour=0, minute=0, second=0, microsecond=0)
    tomorrow_start = today_start + timedelta(days=1)

    tools_total = db.query(func.count()).select_from(Tools).filter(Tools.is_active == True).scalar() or 0
    users_total = db.query(func.count()).select_from(User).filter(User.is_delete == False).scalar() or 0
    visits_today = db.query(func.count()).select_from(LoginSession).filter(LoginSession.create_time >= today_start, LoginSession.create_time < tomorrow_start).scalar() or 0
    tool_clicks_total = db.query(func.sum(Tools.click_count)).scalar() or 0

    def _build_visit_series(n_days: int) -> list:
        series = []
        start_day = today_start - timedelta(days=n_days - 1)
        day = start_day
        while day < tomorrow_start:
            next_day = day + timedelta(days=1)
            value = db.query(func.count()).select_from(LoginSession).filter(LoginSession.create_time >= day, LoginSession.create_time < next_day).scalar() or 0
            series.append({"date": day.strftime("%Y-%m-%d"), "value": value})
            day = next_day
        return series

    visit_trends = {
        "last_7_days": _build_visit_series(7),
        "last_30_days": _build_visit_series(30),
        "last_90_days": _build_visit_series(90),
    }

    latest_tools = db.query(Tools).filter(Tools.is_active == True).order_by(Tools.created_at.desc()).limit(latest_limit).all()
    latest_tools_data = [ToolsResponse.from_orm(t) for t in latest_tools]

    hot_tools_rows = db.query(
        Tools.id.label("tool_id"),
        Tools.title.label("name"),
        Tools.click_count.label("clicks")
    ).order_by(Tools.click_count.desc()).limit(10).all()
    hot_tools_top10 = [{"tool_id": r.tool_id, "name": r.name, "clicks": r.clicks} for r in hot_tools_rows]

    return {
        "code": 200,
        "msg": "success",
        "data": {
            "summary": {
                "tools_total": tools_total,
                "users_total": users_total,
                "visits_today": visits_today,
                "tool_clicks_total": tool_clicks_total,
            },
            "visit_trends": visit_trends,
            "latest_tools": latest_tools_data,
            "hot_tools_top10": hot_tools_top10,
        },
    }
